from sqlmigration.diagnostics import diagnose_sql_error, diagnose_system_error


def _sep(case_title: str, start: bool):
    print("\n" + "-" * 72)
    print(f"{case_title} — {'START' if start else 'END'}")
    print("-" * 72)


def test_diagnose_type_mismatch_suggests_bool_fix():
    _sep("[CASE-DIAG-1] 值类型不匹配提示", True)
    sql = 'INSERT INTO "t" VALUES (1, 1)'
    err = 'value type int doesn\'t match type bool of column "b"'
    print(f"[INPUT] SQL: {sql}\n[INPUT] ERROR: {err}")
    msg = diagnose_sql_error(sql, err, phase='DML')
    print(f"[RESULT] DIAG: \n{msg}")
    assert '类型不匹配' in msg or '不匹配' in msg
    # 输出应包含具体列/表或类型期望/实际的提示
    assert ('列:' in msg) or ('类型:' in msg) or ('表:' in msg)
    print("[OK] 值类型不匹配提示 通过")
    _sep("[CASE-DIAG-1] 值类型不匹配提示", False)


def test_diagnose_kafka_not_available():
    _sep("[CASE-DIAG-2] Kafka 不可用提示", True)
    msg = diagnose_system_error('No brokers available', context='kafka')
    print(f"[INPUT] ERROR: No brokers available\n[RESULT] DIAG:\n{msg}")
    assert 'Kafka 不可用' in msg or 'Kafka' in msg
    print("[OK] Kafka 不可用提示 通过")
    _sep("[CASE-DIAG-2] Kafka 不可用提示", False)


def test_diagnose_sql_syntax_and_missing_objects():
    _sep("[CASE-DIAG-3] 语法/对象缺失提示", True)
    sql = 'CREATE INDEX idx ON "t"("a")'
    err = 'syntax error at or near "ON"'
    print(f"[INPUT] SQL: {sql}\n[INPUT] ERROR: {err}")
    msg = diagnose_sql_error(sql, err, phase='CONSTRAINTS')
    print(f"[RESULT] DIAG: \n{msg}")
    assert '语法' in msg

    err2 = 'relation "t" does not exist'
    print(f"[INPUT] ERROR: {err2}")
    msg2 = diagnose_sql_error(sql, err2, phase='DDL')
    print(f"[RESULT] DIAG2: \n{msg2}")
    assert '不存在' in msg2
    _sep("[CASE-DIAG-3] 语法/对象缺失提示", False)


def test_diagnose_unique_fk_and_check():
    _sep("[CASE-DIAG-4] 唯一/外键/CHECK 提示", True)
    # 唯一约束：使用建约束语句
    sql_uq = 'ALTER TABLE "t" ADD CONSTRAINT uq UNIQUE("a")'
    err_u = 'duplicate key value violates unique constraint "uq"'
    print(f"[INPUT] SQL: {sql_uq}\n[INPUT] ERROR: {err_u}")
    msg_u = diagnose_sql_error(sql_uq, err_u, phase='CONSTRAINTS')
    print(f"[RESULT] DIAG-U: \n{msg_u}")
    assert '唯一约束' in msg_u

    # 外键：使用触发外键失败的 INSERT 语句
    sql_fk = 'INSERT INTO "child"(pid) VALUES (123)'
    err_fk = 'insert or update on table "child" violates foreign key constraint'
    print(f"[INPUT] SQL: {sql_fk}\n[INPUT] ERROR: {err_fk}")
    msg_fk = diagnose_sql_error(sql_fk, err_fk, phase='DML')
    print(f"[RESULT] DIAG-FK: \n{msg_fk}")
    assert '外键' in msg_fk

    # CHECK：使用违反 CHECK 的 INSERT 语句
    sql_chk = 'INSERT INTO "cc"(x) VALUES (-1)'
    err_chk = 'violates check constraint "chk_x"'
    print(f"[INPUT] SQL: {sql_chk}\n[INPUT] ERROR: {err_chk}")
    msg_chk = diagnose_sql_error(sql_chk, err_chk, phase='DML')
    print(f"[RESULT] DIAG-CHK: \n{msg_chk}")
    assert 'CHECK' in msg_chk or '检查' in msg_chk
    _sep("[CASE-DIAG-4] 唯一/外键/CHECK 提示", False)


def test_diagnose_oom_connections_permissions():
    _sep("[CASE-DIAG-5] 连接/权限 提示", True)
    sql = 'CREATE INDEX idx ON "t"("a")'
    err_conn = 'server closed the connection unexpectedly'
    msg_conn = diagnose_sql_error(sql, err_conn, phase='DDL')
    print(f"[INPUT] ERROR: {err_conn}\n[RESULT] DIAG-CONN: \n{msg_conn}")
    assert '连接' in msg_conn

    err_perm = 'permission denied for relation t'
    msg_perm = diagnose_sql_error(sql, err_perm, phase='DDL')
    print(f"[INPUT] ERROR: {err_perm}\n[RESULT] DIAG-PERM: \n{msg_perm}")
    assert '权限' in msg_perm
    _sep("[CASE-DIAG-5] 连接/权限 提示", False)


def test_diagnose_system_fallback():
    _sep("[CASE-DIAG-6] 系统层兜底提示", True)
    msg_fb = diagnose_system_error('some unknown error', context='')
    print(f"[INPUT] ERROR: some unknown error\n[RESULT] DIAG: \n{msg_fb}")
    assert '建议' in msg_fb
    _sep("[CASE-DIAG-6] 系统层兜底提示", False)


def test_diagnose_db_connection_refused():
    _sep("[CASE-DIAG-7] 数据库连接不可用提示", True)
    sql = 'SELECT 1'
    err = 'could not connect to server: Connection refused'
    print(f"[INPUT] SQL: {sql}\n[INPUT] ERROR: {err}")
    msg = diagnose_sql_error(sql, err, phase='DDL')
    print(f"[RESULT] DIAG: \n{msg}")
    assert ('连接' in msg) or ('可达' in msg)
    _sep("[CASE-DIAG-7] 数据库连接不可用提示", False)


def test_diagnose_kafka_brokers_unavailable():
    _sep("[CASE-DIAG-8] Kafka 未开启/不可用提示", True)
    err = 'No brokers available'
    print(f"[INPUT] ERROR: {err}")
    msg = diagnose_system_error(err, context='kafka')
    print(f"[RESULT] DIAG: \n{msg}")
    assert ('Kafka' in msg) and ('建议处理' in msg or '建议' in msg)
    _sep("[CASE-DIAG-8] Kafka 未开启/不可用提示", False)


# 专用 OOM 测试移除

